use std::collections::HashMap;
use std::fmt::{Debug, Write};
use serde_json::Value;
use crate::extends::core::db::sql_dialect::DatabaseDialect;

/// SelectBuilder 用于构建 SQL SELECT 查询
#[derive(Debug, Clone)]
pub struct SelectBuilder {
    sql: String,                          // 保存构建中的 SQL 语句
    params: HashMap<String, Value>,       // 保存查询参数
    dialect: DatabaseDialect,             // 数据库方言（MySQL、PostgreSQL、Oracle 等）
}

impl SelectBuilder {
    /// 创建一个新的 SelectBuilder 实例
    pub fn new(dialect: DatabaseDialect) -> Self {
        SelectBuilder {
            sql: String::new(),
            params: HashMap::new(),
            dialect,
        }
    }

    /// 构建 SELECT 子句
    pub fn select(&mut self, columns: &str) -> &mut Self {
        write!(self.sql, "SELECT {} ", columns).unwrap();
        self
    }

    /// 构建 FROM 子句
    pub fn from_table(&mut self, table: &str) -> &mut Self {
        write!(self.sql, "FROM {} ", table).unwrap();
        self
    }

    /// 构建 JOIN 子句
    fn join(&mut self, table: &str, condition: &str, join_type: &str) -> &mut Self {
        write!(self.sql, "{} JOIN {} ON {} ", join_type, table, condition).unwrap();
        self
    }

    /// 构建 LEFT JOIN 子句
    pub fn left_join(&mut self, table: &str, condition: &str) -> &mut Self {
        self.join(table, condition, "LEFT")
    }

    /// 构建 RIGHT JOIN 子句
    pub fn right_join(&mut self, table: &str, condition: &str) -> &mut Self {
        self.join(table, condition, "RIGHT")
    }

    /// 构建 WHERE 子句的内部方法
    fn where_clause_internal(&mut self, condition: &str, params: HashMap<&str, &Value>, clause_type: &str) -> &mut Self {
        if !self.sql.contains("WHERE") {
            write!(self.sql, "WHERE ").unwrap();
        } else {
            write!(self.sql, " {} ", clause_type).unwrap();
        }

        for (key, value) in params {
            self.params.insert(key.to_string(), value.clone());
        }

        self.sql.push_str(condition);
        self
    }

    /// 构建 WHERE 子句
    pub fn where_clause(&mut self, condition: &str, param_key: &str, param_value: &Value) -> &mut Self {
        let mut params = HashMap::new();
        params.insert(param_key, param_value);
        self.where_clause_internal(condition, params, "AND")
    }

    /// 构建包含多个条件的 WHERE 子句
    pub fn where_clause_map(&mut self, conditions: HashMap<&str, &Value>) -> &mut Self {
        self.where_clause_internal("", conditions, "AND")
    }

    /// 构建 AND 子句
    pub fn and_clause(&mut self, condition: &str, param_key: &str, param_value: &Value) -> &mut Self {
        let mut params = HashMap::new();
        params.insert(param_key, param_value);
        self.where_clause_internal(condition, params, "AND")
    }

    /// 构建 OR 子句
    pub fn or_clause(&mut self, condition: &str, param_key: &str, param_value: &Value) -> &mut Self {
        let mut params = HashMap::new();
        params.insert(param_key, param_value);
        self.where_clause_internal(condition, params, "OR")
    }

    /// 构建 BETWEEN AND 子句
    pub fn between_and<T>(&mut self, field: &str, start: T, end: T) -> &mut Self
        where
            T: ToString,
    {
        let clause = format!("{} BETWEEN '{}' AND '{}'", field, start.to_string(), end.to_string());
        self.where_clause_internal(&clause, HashMap::new(), "AND")
    }

    /// 构建 GROUP BY 子句
    pub fn group_by(&mut self, columns: &str) -> &mut Self {
        write!(self.sql, "GROUP BY {} ", columns).unwrap();
        self
    }

    /// 构建 HAVING 子句
    pub fn having(&mut self, condition: &str, params: HashMap<&str, &Value>) -> &mut Self {
        write!(self.sql, "HAVING {} ", condition).unwrap();
        for (key, value) in params {
            self.params.insert(key.to_string(), value.clone());
        }
        self
    }

    /// 构建 ORDER BY 子句
    pub fn order_by(&mut self, columns: &str, direction: &str) -> &mut Self {
        write!(self.sql, "ORDER BY {} {} ", columns, direction).unwrap();
        self
    }

    /// 构建 LIMIT 子句，支持不同数据库的方言
    pub fn limit(&mut self, start: u16, count: Option<u16>) -> &mut Self {
        match self.dialect {
            DatabaseDialect::MySQL => {
                if let Some(count) = count {
                    write!(self.sql, "LIMIT {}, {} ", start, count).unwrap();
                } else {
                    write!(self.sql, "LIMIT {} ", start).unwrap();
                }
            }
            DatabaseDialect::PostgreSQL => {
                if let Some(count) = count {
                    write!(self.sql, "LIMIT {} OFFSET {} ", count, start).unwrap();
                } else {
                    write!(self.sql, "LIMIT {} ", start).unwrap();
                }
            }
            DatabaseDialect::Oracle => {
                write!(self.sql, "OFFSET {} ROWS ", start).unwrap();
                if let Some(count) = count {
                    write!(self.sql, "FETCH NEXT {} ROWS ONLY ", count).unwrap();
                }
            }
            _ => {
                panic!("Unsupported dialect");
            }
        }
        self
    }

    /// 构建子查询
    pub fn subquery(&mut self, builder: &mut SelectBuilder) -> String {
        let subquery_sql = builder.build();
        for (key, value) in &builder.params {
            self.params.insert(key.clone(), value.clone());
        }
        format!("({})", subquery_sql)
    }

    /// 生成最终的 SQL 查询字符串
    pub fn build(&mut self) -> String {
        let mut compiled_sql = self.sql.clone();
        for (key, value) in &self.params {
            let value_str = match value {
                Value::String(s) => format!("'{}'", s),
                Value::Number(n) => n.to_string(),
                Value::Bool(b) => b.to_string(),
                Value::Null => "NULL".to_string(),
                Value::Array(arr) => format!("'{}'", serde_json::to_string(arr).unwrap()),
                Value::Object(obj) => format!("'{}'", serde_json::to_string(obj).unwrap()),
            };
            compiled_sql = compiled_sql.replace(&format!(":{}", key), &value_str);
        }
        compiled_sql
    }

    /// 生成 COUNT 查询字符串
    pub fn build_count(&self) -> String {
        let mut count_sql = self.sql.clone();
        if let Some(from_index) = count_sql.find("FROM") {
            count_sql = format!("SELECT COUNT(*) as count {}", &count_sql[from_index..]);
        }
        for (key, value) in &self.params {
            let value_str = match value {
                Value::String(s) => format!("'{}'", s),
                Value::Number(n) => n.to_string(),
                Value::Bool(b) => b.to_string(),
                Value::Null => "NULL".to_string(),
                Value::Array(arr) => format!("'{}'", serde_json::to_string(arr).unwrap()),
                Value::Object(obj) => format!("'{}'", serde_json::to_string(obj).unwrap()),
            };
            count_sql = count_sql.replace(&format!(":{}", key), &value_str);
        }
        count_sql
    }
}

pub fn main() {
    // 测试代码可以在此处编写
}
